package com.logic.dbHandler;


import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;
import org.jooq.Record;
import org.jooq.Result;
import org.jooq.SQLDialect;
import org.jooq.impl.Factory;

import com.DBObjects.dbo.Tables;
import com.DBObjects.dbo.tables.*;
import com.DBObjects.dbo.tables.records.SysRoleregisterRecord;
import com.DBObjects.dbo.tables.records.SysUsersRecord;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;

import utl.mofen.dbHandler.DBBaseHandler;
import utl.mofen.dbOperator.ExecuteScript;
import utl.mofen.resultSet.ConvertUtil;
import utl.mofen.utils.GUID;
import utl.mofen.utils.GsonUtil;
import utl.mofen.utils.MD5Util;
import utl.mofen.utils.ResultSetUtil;
import utl.mofen.utils.StringUtil;



/**
 * 用户管理
 * FKey,  用户主键
 * FCode, 用户编号
 * FName, 用户名称
 * FPassWord,  用户密码(MD5加密)
 *  
 * @author Administrator
 *
 */
public class UserCenter extends DBBaseHandler {
	private final Logger logger = Logger.getLogger(this.getClass());

	private static byte[] yhMIXDATA = {35, -46, -69, 35, -70, -29, 35};
    ExecuteScript executer = new ExecuteScript();
    
   
    /**
     * 检测密码用户名和密码是否匹配
     * @param user
     * @param pass
     * @return
     *   -1:用户不存在
     *    1:验证成功
     *    0:密码不匹配
     * @throws Exception 
     */
    private int checkPW(String user, String pass, boolean processSession) throws Exception
    {
    	
		String mix = encodePassword(user, pass);
		
		ResultSet rs = this.dbOpera.queryData("select * from sys_Users WHERE FCode = '" + user + "'");
		int count = ResultSetUtil.getResultSetRecordCount(rs);
		if (count==0)
		{
			return -1;
		}
		
		rs.first();

		//对比密码
		if (rs.getString("FPassword").equalsIgnoreCase(mix))
		{			
			if (processSession)
			{		
				JsonObject json = userRecord2Json(rs);
				json.remove("password");
				this.resultJsonStream.getJSonObject().add("userData", json);
				this.session.setSession("login", true);
			}			
			return 1;
		}else
		{
			if (processSession)
			{
				this.session.setSession("login", false);
			}
			return 0;		
		}    	
    }
    
    /**
     * 编码密码
     * @param user
     * @param pass
     * @return
     *   返回通过用户名和密码进行md5编码的密码
     * @throws IOException 
     */
    private String encodePassword(String user, String pass) throws IOException
    {
    	if (pass==null)
    	{
    		pass = "";
    	}
    	
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		os.write(user.toLowerCase().getBytes());
		os.write(yhMIXDATA);
		os.write(pass.getBytes());		
		String mix = MD5Util.MD5(os.toByteArray());
		os.close();
		return mix;
    }
    
    private void doUserList() throws Exception
    {  	
		ResultSet rs = this.dbOpera.queryData("select * from sys_Users");
		JsonArray jsonList = new JsonArray();
		while (rs.next()) 
		{
			JsonObject json = this.userRecord2Json(rs);
			System.out.println(GsonUtil.json2String(json));
			jsonList.add(json);
		}	
		
		this.resultJsonStream.getJSonObject().add("list", jsonList);		
    }
    
    private JsonObject roleRecord2Json(ResultSet rs) throws Exception
    {
    	JsonObject ret = new JsonObject();
    	ret.addProperty("key", rs.getString("FKey"));
    	ret.addProperty("code", rs.getString("FRole"));
    	ret.addProperty("remark", rs.getString("FRemark"));
    	return ret;    	
    }
    
    
    private JsonObject userRecord2Json(ResultSet rs) throws Exception
    {
    	JsonObject ret = null;
    	System.out.println(rs.getString("FKey"));
    	String s = rs.getString("FJsnData");
    	if (StringUtil.stringIsNotEmpty(s))
    	{
    		try
    		{
    			ret = GsonUtil.parseObject(s);
    		}catch(Exception e)
    		{
    			ret = new JsonObject();
    			this.logger.info(rs.getString("FCode") + "用户信息不合法!");
    			e.printStackTrace();
    		}
    	   
    	}else
    	{
    		ret = new JsonObject();
    	}
    	ret.addProperty("key", rs.getString("FKey"));
    	ret.addProperty("code", rs.getString("FCode"));
    	ret.addProperty("name", rs.getString("FName"));
    	ret.addProperty("password", rs.getString("FPassword"));
    	ret.addProperty("closed", rs.getBoolean("FClosed"));
      	ret.addProperty("remark", rs.getString("FRemark"));
    	return ret;
    }
    
    private void doGetUser() throws Exception
    {
    	String code = "";
    	if (!config.has("code"))
    	{    		
    		throw new Exception("请传入指定的用户名");
    	}    
    	code = config.get("code").getAsString();
		ResultSet rs = this.dbOpera.queryData("select * from sys_Users where fcode='" + code + "'");
	    if(rs.next())
	    {
	    	JsonObject jsonData = userRecord2Json(rs);
	    	this.resultJsonStream.getJSonObject().add("data", jsonData);
	    }else
	    {
	    	throw new Exception("指定的用户信息不存在[" + config.get("code").getAsString() + "]");
	    }
 
    }
    
    private void json2SysRoleRecord(JsonObject roleData, 
    		SysRoleregisterRecord roleRec) throws Exception
    {
		if (roleData.has("key"))
		{    
			String key = roleData.get("key").getAsString();
			if (StringUtil.stringIsNotEmpty(key))
			{
				roleRec.setFkey(roleData.get("key").getAsString());
			}
		}
		if (roleData.has("code"))
		{    
			roleRec.setFrole(roleData.get("code").getAsString());
		}	

		if (roleData.has("closed"))
		{    
			roleRec.setFclosed(roleData.get("closed").getAsBoolean());
		}
		
		if (roleData.has("remark"))
		{    
			roleRec.setFremark(roleData.get("remark").getAsString());
		}	
		
		if (roleData.has("upbuilder"))
		{    
			roleRec.setFupbuilder(roleData.get("remark").getAsString());
		}	
		
		if (roleData.has("upbuilder"))
		{    
			roleRec.setFupbuilder(roleData.get("remark").getAsString());
		}	
		
		if (roleData.has("upbuilderName"))
		{    
			roleRec.setFupbuildername(roleData.get("upbuilderName").getAsString());
		}		

    }
    
    private void json2SysUser(JsonObject userData, 
    		SysUsersRecord userRec) throws Exception
    {
		if(userData.has("password"))
		{
			if (!userData.has("code"))
			{
				throw new Exception("修改密码时需要提供用户名!");
			}
			
			String pass = userData.get("password").getAsString();
			String encodePass = encodePassword(userData.get("code").getAsString(), pass);
			userRec.setFpassword(encodePass);
		}
		if (userData.has("key"))
		{    
			String key = userData.get("key").getAsString();
			if (StringUtil.stringIsNotEmpty(key))
			{
				userRec.setFkey(userData.get("key").getAsString());
			}
		}
		if (userData.has("code"))
		{    
			userRec.setFcode(userData.get("code").getAsString());
		}	
		if (userData.has("name"))
		{    
			userRec.setFname(userData.get("name").getAsString());
		}	
		if (userData.has("closed"))
		{    
			userRec.setFclosed(userData.get("closed").getAsBoolean());
		}
		if (userData.has("remark"))
		{    
			userRec.setFremark(userData.get("remark").getAsString());
		}	
		

		JsonObject lvData = (JsonObject) GsonUtil.clone(userData);
		lvData.remove("key");
		lvData.remove("code");
		lvData.remove("name");
		lvData.remove("password");
		lvData.remove("remark");
		userRec.setFjsndata(GsonUtil.json2String(lvData));			
    }
    
    private void doUpdateRoleUserList(String roleKey, JsonElement roleUserList) throws Exception
    {
		if (StringUtil.stringIsEmpty(roleKey))
		{
			throw new Exception("更新用户组用户列表失败,没有指定用户组主键!");
		}
    	if (GsonUtil.jsonIsNull(roleUserList)) return;    	
		Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
		
		
		
		//清除原有的用户
		create.delete(Tables.SYS_USERROLEREGISTER)
		.where(Tables.SYS_USERROLEREGISTER.FROLEKEY.equal(roleKey))
		.execute();
		
		JsonArray list = roleUserList.getAsJsonArray();
		for (int i=0;i<list.size();i++)
		{		
			JsonObject item = list.get(i).getAsJsonObject();
			String userKey = item.get("userkey").getAsString();
			if (StringUtil.stringIsEmpty(roleKey))
			{
				throw new Exception("用户列表中没有指定用户主键!");
			}
			//插入到角色列表中
			create.insertInto(Tables.SYS_USERROLEREGISTER, 
					Tables.SYS_USERROLEREGISTER.FKEY,
					Tables.SYS_USERROLEREGISTER.FROLEKEY,
					Tables.SYS_USERROLEREGISTER.FUSERKEY)
					.values(GUID.createGUID(),
							roleKey,
							userKey
							).execute();
		}
    }
    
    private void doUpdateUserRole(String userKey, JsonElement userRoles) throws Exception
    {
		if (StringUtil.stringIsEmpty(userKey))
		{
			throw new Exception("更新用户角色列表失败,没有指定用户主键!");
		}
    	if (GsonUtil.jsonIsNull(userRoles)) return;    	
		Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
		
		
		
		//清除原有的角色
		create.delete(Tables.SYS_USERROLEREGISTER)
		.where(Tables.SYS_USERROLEREGISTER.FUSERKEY.equal(userKey))
		.execute();
		
		JsonArray list = userRoles.getAsJsonArray();
		for (int i=0;i<list.size();i++)
		{		
			JsonObject item = list.get(i).getAsJsonObject();
			String roleKey = item.get("roleKey").getAsString();
			if (StringUtil.stringIsEmpty(roleKey))
			{
				throw new Exception("用户角色列表中没有指定角色主键!");
			}
			//插入到角色列表中
			create.insertInto(Tables.SYS_USERROLEREGISTER, 
					Tables.SYS_USERROLEREGISTER.FKEY,
					Tables.SYS_USERROLEREGISTER.FROLEKEY,
					Tables.SYS_USERROLEREGISTER.FUSERKEY)
					.values(GUID.createGUID(),
							roleKey,
							userKey
							).execute();
		}
    }
    
    private void doUserDelete() throws Exception
    {
    	if (!config.has("data"))
    	{    		
    		throw new Exception("请传入要删除的用户数据!");
    	}
    	
    	JsonObject jsonData = config.get("data").getAsJsonObject();
    	
    	if (!jsonData.has("code"))
    	{    		
    		throw new Exception("没有指定要删除的用户编号!");
    	}
    	
    	String code = jsonData.get("code").getAsString();
    	
    	Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
    	
		
		Result<Record> result = create
		.select()
		.from(Tables.SYS_USERS)
		.where(Tables.SYS_USERS.FCODE.equal(code)).fetch();
		
		if (result.size()>0)
		{
			SysUsersRecord r = (SysUsersRecord) result.get(0);
			
			//用户组中的用户
			create.delete(Tables.SYS_USERROLEREGISTER)
			.where(Tables.SYS_USERROLEREGISTER.FUSERKEY.equal(r.getFkey()))
			.execute();
			
			r.delete();
		}
 
    }
    
    
    private void doRoleDelete() throws Exception
    {
    	if (!config.has("data"))
    	{    		
    		throw new Exception("请传入要删除的用户组数据!");
    	}
    	
    	JsonObject jsonData = config.get("data").getAsJsonObject();
    	
    	if (!jsonData.has("code"))
    	{    		
    		throw new Exception("没有指定要删除用户组名称!");
    	}
    	
    	String code = jsonData.get("code").getAsString();
    	
    	Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
    	
		
		Result<Record> result = create
		.select()
		.from(Tables.SYS_ROLEREGISTER)
		.where(Tables.SYS_ROLEREGISTER.FROLE.equal(code)).fetch();
		
		if (result.size()>0)
		{

			SysRoleregisterRecord r = (SysRoleregisterRecord) result.get(0);
			
			//清除原有的用户
			create.delete(Tables.SYS_USERROLEREGISTER)
			.where(Tables.SYS_USERROLEREGISTER.FROLEKEY.equal(r.getFkey()))
			.execute();
			
			r.delete();
		}
 
    }
    
    private void doRoleAdd() throws Exception
    {
    	if (!config.has("data"))
    	{    		
    		throw new Exception("请传入要保存的用户组数据!");
    	}
    	JsonObject jsonData = config.get("data").getAsJsonObject();
    	
    	if (!jsonData.has("code"))
    	{    		
    		throw new Exception("没有指定要更新用户组名称!");
    	}
    	
    	String code = jsonData.get("code").getAsString();
    	
    	Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
    	
		
		Result<Record> result = create
		.select()
		.from(Tables.SYS_ROLEREGISTER)
		.where(Tables.SYS_ROLEREGISTER.FROLE.equal(code)).fetch();
		
		if (result.size()>0)
		{
			throw new Exception("用户组已经存在!");			
		}

		SysRoleregisterRecord r = create.newRecord(Tables.SYS_ROLEREGISTER);
		System.out.println(GsonUtil.json2String(jsonData));
		json2SysRoleRecord(jsonData, r);
		r.store();
		doUpdateRoleUserList(r.getFkey(), jsonData.get("users"));
 
    }
    
    private void doRoleUpdate() throws Exception
    {
    	if (!config.has("data"))
    	{    		
    		throw new Exception("请传入要保存的用户组数据!");
    	}
    	JsonObject jsonData = config.get("data").getAsJsonObject();
    	
    	if (!jsonData.has("code"))
    	{    		
    		throw new Exception("没有指定要更新用户组名称!");
    	}
    	
    	String code = jsonData.get("code").getAsString();
    	
    	Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
		
		Result<Record> result = create
		.select()
		.from(Tables.SYS_ROLEREGISTER)
		.where(Tables.SYS_ROLEREGISTER.FROLE.equal(code)).fetch();
		
		if (result.size()==0)
		{
			throw new Exception("要更新的用户组信息不存在, 可能被删除!");			
		}
		SysRoleregisterRecord r = (SysRoleregisterRecord) result.get(0);
		System.out.println(GsonUtil.json2String(jsonData));
		json2SysRoleRecord(jsonData, r);
		r.store();
		doUpdateRoleUserList(r.getFkey(), 
				jsonData.get("users"));
    }
    
    private void doUserUpdate() throws Exception
    {
    	if (!config.has("data"))
    	{    		
    		throw new Exception("请传入要保存的数据!");
    	}
    	
    	JsonObject jsonData = config.get("data").getAsJsonObject();
    	
    	if (!jsonData.has("code"))
    	{    		
    		throw new Exception("没有指定要更新用户名!");
    	}
    	
		Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
		
		Result<Record> result = create
		.select()
		.from(SysUsers.SYS_USERS)
		.where(SysUsers.SYS_USERS.FCODE.equal(jsonData.get("code").getAsString())).fetch();
		
		if (result.size()==0)
		{
			throw new Exception("要更新的用户信息不存在可能被删除!");			
		}
		SysUsersRecord r = (SysUsersRecord) result.get(0);
		System.out.println(GsonUtil.json2String(jsonData));
		json2SysUser(jsonData, r);
		r.store();		
		
		doUpdateUserRole(r.getFkey(), 
				jsonData.get("roles"));
 
    }
    
    private void doUserAdd() throws Exception
    {
    	if (!config.has("data"))
    	{    		
    		throw new Exception("请传入要保存的数据!");
    	}
    	
    	JsonObject jsonData = config.get("data").getAsJsonObject();
    	
    	if (!jsonData.has("code"))
    	{    		
    		throw new Exception("没有设定用户名!");
    	}
    	
    	
		Factory create = new Factory(dbOpera.getConnection(),
				SQLDialect.SQLSERVER);
		
		Result<Record> result = create
		.select()
		.from(SysUsers.SYS_USERS)
		.where(SysUsers.SYS_USERS.FCODE.equal(jsonData.get("code").getAsString())).fetch();
		
		if (result.size()>0)
		{
			throw new Exception("用户名已经存在!");			
		}

		SysUsersRecord r = create.newRecord(SysUsers.SYS_USERS);
		System.out.println(GsonUtil.json2String(jsonData));
		json2SysUser(jsonData, r);
		r.store();
		doUpdateUserRole(r.getFkey(), jsonData.get("roles"));
 
    }
    
    private void doLogOut() throws Exception
    {
    	if (!config.has("code"))
    	{    		
    		throw new Exception("请传入要登出的用户名");
    	}    	
		this.session.setSession("login", false);		
    }
    
    private void doChangePW() throws Exception
    {
    	if (!config.has("code"))
    	{    		
    		throw new Exception("请传入要验证的用户名");
    	}
    	
    	String code = config.get("code").getAsString();    	
    	String pass = "";
    	
    	if (!config.has("oldPassword"))
    	{    		
    		throw new Exception("请传入旧密码!");    		
    	}else
    	{
    		int r = checkPW(code, config.get("oldPassword").getAsString(), false);
    		if (r!=1)
    		{
    			throw new Exception("身份验证失败!"); 
    		}
    	}
    	
    	if (!config.has("password"))
    	{
    		throw new Exception("请传入要修改的密码!");    		
    	}
    	
    	pass = config.get("password").getAsString();
		
	
		ResultSet rs = this.dbOpera.queryData("select * from sys_Users WHERE FCode = '" + code + "'");
		int count = ResultSetUtil.getResultSetRecordCount(rs);
		if (count==0)
		{
			throw new Exception("用户不存在!");
		}
		rs.first();
		
		String mix = encodePassword(code, pass);	

		
		String sql = "update sys_Users set FPassword = '" + mix 
				     + "' where FKey = '" + rs.getString("FKey") + "'";
		
		this.dbOpera.executeSQL(sql);
		this.logger.info(code +  "修改密码成功!");
    }
    
    private void doLogin(boolean packUserData) throws Exception
    {
    	if (!config.has("code"))
    	{    		
    		throw new Exception("请传入要验证的用户名");
    	}
    	
    	String code = config.get("code").getAsString();    	
    	String pass = "";
    	
    	if (config.has("password"))
    	{
    		pass = config.get("password").getAsString();    		
    	}
    	
    	int r = checkPW(code, pass, true);
    	if (r==-1)
    	{
    		throw new Exception("找不到对应的用户信息");
    	}else if (r==0)
    	{
    		throw new Exception("密码不匹配");
    	}
    	JsonArray roles = getUserRoleList(code);
    	this.resultJsonStream.getJSonObject().get("userData").getAsJsonObject().add("list", roles);
	}

    private JsonArray getRoleUserList(String code) throws Exception
    {
    	if (StringUtil.stringIsEmpty(code))
    	{
    		throw new Exception("没有指定用户组编号!");
    	}
    	this.sqlParser.clear();
    	this.sqlParser.setScriptKey(10010202);
    	this.sqlParser.setScriptStep(20);
    	this.sqlParser.setParam("@mm_Code", code);
    	
    	JsonArray jsonList = new JsonArray();
    	JsonObject item = null;
    	
    	String sql = this.sqlParser.getScript();
    	ResultSet rs = this.dbOpera.queryData(sql);
    	while (rs.next())
    	{
    		item = new JsonObject();
    		item.addProperty("key", rs.getString("FUserKey"));
    		item.addProperty("code", rs.getString("FUserCode"));
    		item.addProperty("name", rs.getString("FUserName"));
    		jsonList.add(item);
    	}
    	return jsonList;
    }
    
    private JsonArray getUserRoleList(String code) throws Exception
    {
    	if (StringUtil.stringIsEmpty(code))
    	{
    		throw new Exception("没有指定用户帐号!");
    	}
    	this.sqlParser.clear();
    	this.sqlParser.setScriptKey(10010202);
    	this.sqlParser.setScriptStep(10);
    	this.sqlParser.setParam("@mm_Code", code);
    	
    	JsonArray jsonList = new JsonArray();
    	JsonObject item = null;
    	
    	String sql = this.sqlParser.getScript();
    	ResultSet rs = this.dbOpera.queryData(sql);
    	while (rs.next())
    	{
    		item = new JsonObject();
    		item.addProperty("key", rs.getString("FRoleKey"));
    		item.addProperty("code", rs.getString("FRole"));
    		jsonList.add(item);
    	}
    	return jsonList;
    }
    
    private void doGetUserRoleList() throws Exception {
    	if (!config.has("code"))
    	{    		
    		throw new Exception("请传入要获取的用户名");
    	}
    	
    	String code = config.get("code").getAsString();
    	
    	
    	this.resultJsonStream.getJSonObject().add("list", getUserRoleList(code));  	
    }
    
    private void doGetRoleUserList() throws Exception {
    	if (!config.has("code"))
    	{    		
    		throw new Exception("请指定用户组编号");
    	}
    	
    	String code = config.get("code").getAsString();
    	
    	
    	this.resultJsonStream.getJSonObject().add("list", getRoleUserList(code));  	
    }
    
    private void doGetRoleList() throws Exception {
    	
    	
    	JsonArray jsonList = new JsonArray();
    	JsonObject item = null;
    	
    	String sql = "select * from sys_RoleRegister";
    	ResultSet rs = this.dbOpera.queryData(sql);
    	while (rs.next())
    	{
    		item = roleRecord2Json(rs);
    		jsonList.add(item);
    	}
    	
    	this.resultJsonStream.getJSonObject().add("list", jsonList);  	
    	
    }
    
    private void doGetRole() throws Exception {
    	
    	if (!config.has("code"))
    	{    		
    		throw new Exception("请传入要获取的角色编号");
    	}
    	
    	String code = config.get("code").getAsString();
    	
    	String sql = "select * from sys_RoleRegister where FRole='" + code + "'";
    	ResultSet rs = this.dbOpera.queryData(sql);
    	rs.first();    	
    	this.resultJsonStream.getJSonObject().add("data", this.roleRecord2Json(rs));    	
    }
	
	public void doAction() throws Exception {	
		
//		{     //执行SQL
//		     "plugin":
//		      {
//		        "name":"userCentre"
//		      },
		
//	         "action":10,   //用户修改密码
//	         "code":"admin",	
//           "oldPassword":"old",   //旧密码		
//		     "password":"admin",
		
//	         "action":11,   //用户检测密码
//	         "code":"admin",
//	         "password":"admin"	
		
		
//		     "action":13,   //用户登陆
//		     "code":"admin",
//		     "password":"admin"	
		
		
//		     "action":14,   //用户注销
//		     "code":"admin"		
//		}		
		JsonObject json = config;
		
		int action = json.get("action").getAsInt();
		
		switch (action) {
			case Command.USER_LOGIN:
				doLogin(true);
				break;
			case Command.USER_LOGOUT:
				doLogOut();
				break;		
			case Command.USER_CHNAGEPW:
				doChangePW();
				break;
			case Command.USER_LIST:
				doUserList();
				break;	
			case Command.USER_GET:
				doGetUser();
				break;		
			case Command.USER_ROLES:
				doGetUserRoleList();
				break;			
			case Command.USER_ADD:
				doUserAdd();
				break;	
			case Command.USER_UPDATE:
				doUserUpdate();
				break;
			case Command.USER_DELETE:
				doUserDelete();
				break;
			case Command.ROLE_GET:
				doGetRole();
				break;
			case Command.ROLE_LIST:
				doGetRoleList();
				break;		
			case Command.ROLE_USERS:
				doGetRoleUserList();
				break;	
			case Command.ROLE_UPDATE:
				doRoleUpdate();
				break;	
			case Command.ROLE_ADD:
				doRoleAdd();
				break;	
			case Command.ROLE_DELETE:
				doRoleDelete();
				break;
			default:
				throw new Exception("用户管理插件中未处理的命令:" + action);
		}
	}
}
